<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
	pageEncoding="ISO-8859-1"%>
<%@page import="java.util.*"%>
<%@page import="support.*"%>
<%@page import="java.io.*"%>
<%@page import="java.util.*"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.Statement"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Confirmation Page</title>
</head>
<body>
	<p>
		<%
			String first = session.getAttribute("FirstName").toString();
			String middle = session.getAttribute("MiddleI").toString();
			String last = session.getAttribute("LastName").toString();
			String citizenship = session.getAttribute("CountryOfCitizenship")
					.toString();
			String resident = session.getAttribute("CountryOfResident")
					.toString();
			String USResident = session.getAttribute("USResident").toString();
			String street = session.getAttribute("StreetAddress").toString();
			String city = session.getAttribute("City").toString();
			String state = session.getAttribute("AddressState").toString();
			String zip = session.getAttribute("ZipCode").toString();
			String area = session.getAttribute("AreaCode").toString();
			String specialization = session.getAttribute("specailization")
					.toString();
		%>
		Hello,
		<%=session.getAttribute("FirstName")%>
		<%=session.getAttribute("MiddleI")%>
		<%=session.getAttribute("LastName")%>

		<%
			Connection conn = null;
			String sqlName = "INSERT INTO name(first, middle, last) values(?,?,?)";
			String sqlCountry = "INSERT INTO country(citizenship, residence, residency) values(?,?,?)";
			String sqlAddress = "INSERT INTO address(street, city, state, zip, areacode) values(?,?,?,?,?)";
			String sqlMain = "INSERT INTO maintable(nameid, addid, specid, counid) values(?,?,?,?)";
			String sqlUniversity = "INSERT INTO universities(university, country) values(?,?)";
			String sqlDiscipline = "INSERT INTO discipline(major) values(?)";
			String sqlDegree = "INSERT INTO degree(school, major, month, year, title, gpa, tableid) values(?,?,?,?,?,?,?)";
			
			Statement stmt = null;
			PreparedStatement pstmt = null;
			ResultSet rset = null;
			int id1 = 0;
			int id2 = 0;
			int flag1 = 0;
			int flag2 = 0;
			int idname = 0;
			int idCountry = 0;
			int idAddress = 0;
			int idSpecialization = 0;
			int idMain = 0;

			try {
				conn = DBUtils.getConnection();
				DBUtils.beginTransaction(conn);
				stmt = conn.createStatement();

				//Save info to table name
				pstmt = conn.prepareStatement(sqlName, Statement.RETURN_GENERATED_KEYS);
				pstmt.setString(1, first);
				pstmt.setString(2, middle);
				pstmt.setString(3, last);
				pstmt.executeUpdate();

				//Find id in table name
				rset = pstmt.getGeneratedKeys();
				rset.next();
				idname = rset.getInt(1);

				//Save info to table country
				rset = stmt
						.executeQuery("SELECT id FROM countries WHERE name = '"
								+ citizenship + "'");
				rset.next();
				id1 = rset.getInt(1);

				rset = stmt
						.executeQuery("SELECT id FROM countries WHERE name = '"
								+ resident + "'");
				rset.next();
				id2 = rset.getInt(1);

				pstmt = conn.prepareStatement(sqlCountry, Statement.RETURN_GENERATED_KEYS);
				pstmt.setInt(1, id1);
				pstmt.setInt(2, id2);
				pstmt.setString(3, USResident);
				pstmt.executeUpdate();

				//Find id in table country
				rset = pstmt.getGeneratedKeys();
				rset.next();
				idCountry = rset.getInt(1);

				//Save info to table address
				pstmt = conn.prepareStatement(sqlAddress, Statement.RETURN_GENERATED_KEYS);
				pstmt.setString(1, street);
				pstmt.setString(2, city);
				pstmt.setString(3, state);
				pstmt.setString(4, zip);
				pstmt.setString(5, area);
				pstmt.executeUpdate();

				//Find id in table address
				rset = pstmt.getGeneratedKeys();
				rset.next();
				idAddress = rset.getInt(1);

				//Find id in table specailization
				rset = stmt
						.executeQuery("SELECT id FROM specialization WHERE specialization = '"
								+ specialization + "'");
				rset.next();
				idSpecialization = rset.getInt(1);

				//Save info to table main
				pstmt = conn.prepareStatement(sqlMain, Statement.RETURN_GENERATED_KEYS);
				pstmt.setInt(1, idname);
				pstmt.setInt(2, idAddress);
				pstmt.setInt(3, idSpecialization);
				pstmt.setInt(4, idCountry);
				pstmt.executeUpdate();

				//Find id in table main
				rset = pstmt.getGeneratedKeys();
				rset.next();
				idMain = rset.getInt(1);

				//Save info to table degree
				rset = stmt
						.executeQuery("SELECT id FROM universities WHERE country = '"
								+ session.getAttribute("location").toString()
								+ "' AND university = '"
								+ session.getAttribute("university").toString()
								+ "'");
				while (rset.next()) {
					flag1 = 1;
					id1 = rset.getInt(1);
				}
				if (flag1 == 0) {
					pstmt = conn.prepareStatement(sqlUniversity, Statement.RETURN_GENERATED_KEYS);
					pstmt.setString(1, session.getAttribute("university")
							.toString());
					pstmt.setString(2, session.getAttribute("location")
							.toString());
					pstmt.executeUpdate();
					
					rset = pstmt.getGeneratedKeys();
					rset.next();
					id1 = rset.getInt(1);
				}

				rset = stmt
						.executeQuery("SELECT id FROM discipline WHERE major = '"
								+ session.getAttribute("degreeDiscipline")
										.toString() + "'");
				while (rset.next()) {
					flag2 = 1;
					id2 = rset.getInt(1);
				}
				if (flag2 == 0) {
					pstmt = conn.prepareStatement(sqlDiscipline, Statement.RETURN_GENERATED_KEYS);
					pstmt.setString(1, session.getAttribute("degreeDiscipline")
							.toString());
					pstmt.executeUpdate();
					
					rset = pstmt.getGeneratedKeys();
					rset.next();
					id2 = rset.getInt(1);
				}

				pstmt = conn.prepareStatement(sqlDegree);
				pstmt.setInt(1, id1);
				pstmt.setInt(2, id2);
				pstmt.setString(3, session.getAttribute("month").toString());
				pstmt.setString(4, session.getAttribute("year").toString());
				pstmt.setString(5, session.getAttribute("GPA").toString());
				pstmt.setString(6, session.getAttribute("Title").toString());
				pstmt.setInt(7, idMain);
				pstmt.executeUpdate();

				//Save loop info to table degree
				Vector<university> uni = (Vector<university>) session
						.getAttribute("uni");
				for (int i = 0; i < uni.size(); i++) {
					flag1 = 0;
					flag2 = 0;
					
					rset = stmt
							.executeQuery("SELECT id FROM universities WHERE country = '"
									+ uni.get(i).getLocation()
									+ "' AND university = '"
									+ uni.get(i).getUniversity() + "'");
					while (rset.next()) {
						flag1 = 1;
						id1 = rset.getInt(1);
					}
					if (flag1 == 0) {
						pstmt = conn.prepareStatement(sqlUniversity, Statement.RETURN_GENERATED_KEYS);
						pstmt.setString(1, uni.get(i).getUniversity());
						pstmt.setString(2, uni.get(i).getLocation());
						pstmt.executeUpdate();
						
						rset = pstmt.getGeneratedKeys();
						rset.next();
						id1 = rset.getInt(1);
					}

					rset = stmt
							.executeQuery("SELECT id FROM discipline WHERE major = '"
									+ uni.get(i).getDegreeDiscipline() + "'");
					while (rset.next()) {
						flag2 = 1;
						id2 = rset.getInt(1);
					}
					if (flag2 == 0) {
						pstmt = conn.prepareStatement(sqlDiscipline, Statement.RETURN_GENERATED_KEYS);
						pstmt.setString(1, uni.get(i).getDegreeDiscipline());
						pstmt.executeUpdate();
						
						rset = pstmt.getGeneratedKeys();
						rset.next();
						id2 = rset.getInt(1);
					}

					pstmt = conn.prepareStatement(sqlDegree);
					pstmt.setInt(1, id1);
					pstmt.setInt(2, id2);
					pstmt.setString(3, uni.get(i).getMonth());
					pstmt.setString(4, uni.get(i).getYear());
					pstmt.setString(5, uni.get(i).getGPA());
					pstmt.setString(6, uni.get(i).getTitle());
					pstmt.setInt(7, idMain);
					pstmt.executeUpdate();
				}

				//update table users of maintable id
				String username = request.getUserPrincipal().getName();
				String sqlRole = "UPDATE users set tableid=? where user_name=?";
				pstmt = conn.prepareStatement(sqlRole);
				pstmt.setInt(1, idMain);
				pstmt.setString(2, username);
				pstmt.executeUpdate();
				
				DBUtils.commit(conn);
			} catch (SQLException e) {
				System.out.println(e.toString());
				throw new DaoException("Error on saving data", e);
			} catch (ServiceException e) {
				throw e;
			} catch (Exception e) {
				DBUtils.rollback(conn);
				throw new ServiceException("error occur saving data", e);
			} finally {
				DBUtils.closeStatement(pstmt);
				DBUtils.closeConnection(conn);
			}
		%>
	
	<p>
		Thanks for your application. Your application ID is:
		<%=idMain%>
		<%
			java.text.DateFormat df = new java.text.SimpleDateFormat(
					"dd/MM/yyyy");
		%>
	
	<p>
		Current Date:
		<%=df.format(new java.util.Date())%>
</body>
</html>